package com.study.dao.impl;

import com.study.dao.AwardInfoDAO;
import com.study.dto.AwardDto;
import com.study.dto.MedalDto;
import com.study.dto.MedalSearchDto;
import com.study.pojo.AwardInfo;
import com.study.util.JdbcUtil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @PackgeName: com.study.dao.impl
 * @ClassName: AwardInfoDAOImpl
 * @Author: 严志祥
 * Date: 2022-05-04 19:20
 * project name: web_project_medal
 * @Version:
 * @Description:
 */
public class AwardInfoDAOImpl implements AwardInfoDAO {

    Connection conn = JdbcUtil.getConn();
    @Override
    public int insert(AwardInfo entity) throws SQLException {
        String sql = "insert into award_info (sporting_id,athletes_id,award_ranking,award_grade,award_date)" +
                " values (?,?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,entity.getSportingId());
        pstmt.setObject(2,entity.getAthletesId());
        pstmt.setObject(3,entity.getAwardRanking());
        pstmt.setObject(4,entity.getAwardGrade());
        pstmt.setObject(5,entity.getAwardDate());
        return pstmt.executeUpdate();
    }

    @Override
    public int updateById(AwardInfo entity) throws SQLException {
        String sql = "update award_info set sporting_id = ?,athletes_id=?,award_ranking=?,award_grade=?,award_date=? where award_id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,entity.getSportingId());
        pstmt.setObject(2,entity.getAthletesId());
        pstmt.setObject(3,entity.getAwardRanking());
        pstmt.setObject(4,entity.getAwardGrade());
        pstmt.setObject(5,entity.getAwardDate());
        pstmt.setObject(6,entity.getAwardId());
        return pstmt.executeUpdate();
    }

    @Override
    public int deleteById(Serializable id) throws SQLException {
        String sql = "delete from award_info where award_id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,id);
        return pstmt.executeUpdate();
    }

    @Override
    public AwardDto selectById(Serializable id) throws SQLException {
        String sql = "select * from award_info where award_id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,id);
        ResultSet rs = pstmt.executeQuery();
        AwardDto a = new AwardDto();
        if(rs.next()){
            a.setAwardId(rs.getInt("award_id"));
            a.setSportingId(rs.getInt("sporting_id"));
            a.setAthletesId(rs.getInt("athletes_id"));
            a.setAwardRanking(rs.getInt("award_ranking"));
            a.setAwardGrade(rs.getString("award_grade"));
            a.setAwardDate(rs.getDate("award_date"));
        }
        return a;
    }

    @Override
    public List<AwardInfo> select(int index, int len) throws SQLException {
        String sql = "select * from award_info order by award_id desc limit ?,?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,index);
        pstmt.setObject(2,len);
        ResultSet rs = pstmt.executeQuery();
        List<AwardInfo> list = new ArrayList<>();
        while(rs.next()){
            AwardDto a = new AwardDto();
            a.setAwardId(rs.getInt("award_id"));
            a.setSportingId(rs.getInt("sporting_id"));
            a.setAthletesId(rs.getInt("athletes_id"));
            a.setAwardRanking(rs.getInt("award_ranking"));
            a.setAwardGrade(rs.getString("award_grade"));
            a.setAwardDate(rs.getDate("award_date"));
            list.add(a);
        }
        return list;
    }

    @Override
    public long count() throws SQLException {
        String sql = "select count(*) from award_info";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        rs.next();
        int rsInt = rs.getInt("count(*)");
        return rsInt;
    }

    @Override
    public List<AwardDto> selectMap(int index, int len) throws SQLException {
        String sql = "select\n" +
                "    ai.*,\n" +
                "    a.athletes_img,\n" +
                "    a.athletes_name,\n" +
                "    c.country_name,\n" +
                "    c.country_code,\n" +
                "    c.country_uri,\n" +
                "    se.sporting_name\n" +
                "from athletes_info a\n" +
                "inner join country_info c\n" +
                "on a.country_code = c.country_code\n" +
                "inner join award_info ai\n" +
                "on a.athletes_id = ai.athletes_id\n" +
                "inner join sporting_events se\n" +
                "on ai.sporting_id = se.sporting_id order by ai.award_date desc,ai.award_id desc limit ?,?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,index);
        pstmt.setObject(2,len);
        ResultSet rs = pstmt.executeQuery();
        List<AwardDto> list = new ArrayList<>();
        while(rs.next()){
            AwardDto a = new AwardDto();
            a.setAwardId(rs.getInt("award_id"));
            a.setSportingId(rs.getInt("sporting_id"));
            a.setAthletesId(rs.getInt("athletes_id"));
            a.setAwardRanking(rs.getInt("award_ranking"));
            a.setAwardGrade(rs.getString("award_grade"));
            a.setAwardDate(rs.getDate("award_date"));
            a.setAthletesName(rs.getString("athletes_name"));
            a.setAthletesImg(rs.getString("athletes_img"));
            a.setCountryCode(rs.getString("country_code"));
            a.setCountryName(rs.getString("country_name"));
            a.setCountryUri(rs.getString("country_uri"));
            a.setSportingName(rs.getString("sporting_name"));
            list.add(a);
        }
        return list;
    }

    @Override
    public List<AwardDto> selectSearch(MedalSearchDto medalSearchDto) throws SQLException {
        List<Object> list = new ArrayList<>();
        String where = where(medalSearchDto, list);
        String sql = "select\n" +
                "    ai.*,\n" +
                "    a.athletes_img,\n" +
                "    a.athletes_name,\n" +
                "    c.country_name,\n" +
                "    c.country_code,\n" +
                "    c.country_uri,\n" +
                "    se.sporting_name\n" +
                "from athletes_info a\n" +
                "inner join country_info c\n" +
                "on a.country_code = c.country_code\n"+
                "inner join award_info ai\n"+
                "on a.athletes_id = ai.athletes_id\n" +
                "inner join sporting_events se\n" +
                "on ai.sporting_id = se.sporting_id "+where+" order by ai.award_date desc,ai.award_ranking desc, ai.award_id desc limit ?,?";
        list.add((medalSearchDto.getCurrent()-1)*medalSearchDto.getMaxRecord());
        list.add(medalSearchDto.getMaxRecord());
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,list.get(0));
        pstmt.setObject(2,list.get(1));
        ResultSet rs = pstmt.executeQuery();
        List<AwardDto> l = new ArrayList<>();
        while(rs.next()){
            AwardDto m = new AwardDto();
            m.setAwardId(rs.getInt("award_id"));
            m.setSportingId(rs.getInt("sporting_id"));
            m.setAthletesId(rs.getInt("athletes_id"));
            m.setAwardRanking(rs.getInt("award_ranking"));
            m.setAwardGrade(rs.getString("award_grade"));
            m.setAwardDate(rs.getDate("award_date"));
            m.setAthletesName(rs.getString("athletes_name"));
            m.setAthletesImg(rs.getString("athletes_img"));
            m.setCountryCode(rs.getString("country_code"));
            m.setCountryName(rs.getString("country_name"));
            m.setCountryUri(rs.getString("country_uri"));
            m.setSportingName(rs.getString("sporting_name"));
            l.add(m);
        }
        return l;
    }

    private String where(MedalSearchDto medalSearchDto, List<Object> list) {
        StringBuilder sb = new StringBuilder();
        if(medalSearchDto.getKey()!=null&&!"".equals(medalSearchDto.getKey())) {
            sb.append(" AND a.athletes_name LIKE CONCAT('%',?,'%') ");
            list.add(medalSearchDto.getKey());
        }
        if(medalSearchDto.getCountryCode()!=null && !"".equals(medalSearchDto.getCountryCode())){
            sb.append(" and c.country_code=? ");
            list.add(medalSearchDto.getCountryCode());
        }
        if(medalSearchDto.getAwardRanking()!=null){
            sb.append(" and ai.award_ranking=? ");
            list.add(medalSearchDto.getAwardRanking());
        }
        if(medalSearchDto.getSportingId()!=null){
            sb.append(" and se.sporting_id=? ");
            list.add(medalSearchDto.getSportingId());
        }
        if(medalSearchDto.getAthletesId()!=null){
            sb.append(" and a.athletes_id=? ");
            list.add(medalSearchDto.getAthletesId());
        }
        return sb.toString();
    }

    @Override
    public long countSearch(MedalSearchDto medalSearchDto) throws SQLException {
        List<Object> list = new ArrayList<>();
        String where = where(medalSearchDto, list);
        String sql = "select " +
                "count(*) " +
                "from athletes_info a\n" +
                "inner join country_info c\n" +
                "on a.country_code = c.country_code\n" +
                "inner join award_info ai\n" +
                "on a.athletes_id = ai.athletes_id\n" +
                "inner join sporting_events se\n" +
                "on ai.sporting_id = se.sporting_id"+where;
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        rs.next();
        long c = rs.getInt("count(*)");
        return c;
    }

    @Override
    public List<MedalDto> selectAward1(String countryCode) throws SQLException {
        String sql = "select\n" +
                "*\n" +
                "from \n" +
                "sporting_events sp\n" +
                "inner join\n" +
                "(select \n" +
                "c.country_uri,\n" +
                "c.country_name,\n" +
                "c.country_code,\n" +
                "a.athletes_name,\n" +
                "aw.award_ranking,\n" +
                "aw.sporting_id,\n" +
                "aw.award_date\n" +
                "from \n" +
                "country_info c \n" +
                "inner join \n" +
                "athletes_info a \n" +
                "on \n" +
                "c.country_code = a.country_code\n" +
                "inner join \n" +
                "award_info aw\n" +
                "on\n" +
                "a.athletes_id = aw.athletes_id) tb1\n" +
                "on\n" +
                "sp.sporting_id = tb1.sporting_id\n" +
                "where tb1.country_code = ? and award_ranking = 1";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,countryCode);
        ResultSet rs = pstmt.executeQuery();
        List<MedalDto> list = new ArrayList<>();
        while(rs.next()){
            MedalDto m = new MedalDto();
            m.setCountryName(rs.getString("country_name"));
            m.setSportingName(rs.getString("sporting_name"));
            m.setAwardDate(rs.getDate("award_date"));
            m.setAthletesName(rs.getString("athletes_name"));
            m.setCountryUri(rs.getString("country_uri"));
            list.add(m);
        }
        return list;
    }

    @Override
    public List<MedalDto> selectAward2(String countryCode) throws SQLException {
        String sql = "select\n" +
                "*\n" +
                "from \n" +
                "sporting_events sp\n" +
                "inner join\n" +
                "(select \n" +
                "c.country_uri,\n" +
                "c.country_name,\n" +
                "c.country_code,\n" +
                "a.athletes_name,\n" +
                "aw.award_ranking,\n" +
                "aw.sporting_id,\n" +
                "aw.award_date\n" +
                "from \n" +
                "country_info c \n" +
                "inner join \n" +
                "athletes_info a \n" +
                "on \n" +
                "c.country_code = a.country_code\n" +
                "inner join \n" +
                "award_info aw\n" +
                "on\n" +
                "a.athletes_id = aw.athletes_id) tb1\n" +
                "on\n" +
                "sp.sporting_id = tb1.sporting_id\n" +
                "where tb1.country_code = ? and award_ranking = 2";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,countryCode);
        ResultSet rs = pstmt.executeQuery();
        List<MedalDto> list = new ArrayList<>();
        while(rs.next()){
            MedalDto m = new MedalDto();
            m.setCountryName(rs.getString("country_name"));
            m.setSportingName(rs.getString("sporting_name"));
            m.setAwardDate(rs.getDate("award_date"));
            m.setAthletesName(rs.getString("athletes_name"));
            m.setCountryUri(rs.getString("country_uri"));
            list.add(m);
        }
        return list;
    }

    @Override
    public List<MedalDto> selectAward3(String countryCode) throws SQLException {
        String sql = "select\n" +
                "*\n" +
                "from \n" +
                "sporting_events sp\n" +
                "inner join\n" +
                "(select \n" +
                "c.country_uri,\n" +
                "c.country_name,\n" +
                "c.country_code,\n" +
                "a.athletes_name,\n" +
                "aw.award_ranking,\n" +
                "aw.sporting_id,\n" +
                "aw.award_date\n" +
                "from \n" +
                "country_info c \n" +
                "inner join \n" +
                "athletes_info a \n" +
                "on \n" +
                "c.country_code = a.country_code\n" +
                "inner join \n" +
                "award_info aw\n" +
                "on\n" +
                "a.athletes_id = aw.athletes_id) tb1\n" +
                "on\n" +
                "sp.sporting_id = tb1.sporting_id\n" +
                "where tb1.country_code = ? and award_ranking = 3";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,countryCode);
        ResultSet rs = pstmt.executeQuery();
        List<MedalDto> list = new ArrayList<>();
        while(rs.next()){
            MedalDto m = new MedalDto();
            m.setCountryName(rs.getString("country_name"));
            m.setSportingName(rs.getString("sporting_name"));
            m.setAwardDate(rs.getDate("award_date"));
            m.setAthletesName(rs.getString("athletes_name"));
            m.setCountryUri(rs.getString("country_uri"));
            list.add(m);
        }
        return list;
    }

    @Override
    public List<MedalDto> selectSporting(String sportingName) throws SQLException {
        String sql = "select\n" +
                "*\n" +
                "from \n" +
                "sporting_events sp\n" +
                "inner join\n" +
                "(select \n" +
                "c.country_uri,\n" +
                "c.country_name,\n" +
                "c.country_code,\n" +
                "a.athletes_name,\n" +
                "aw.award_ranking,\n" +
                "aw.sporting_id,\n" +
                "aw.award_date\n" +
                "from \n" +
                "country_info c \n" +
                "inner join \n" +
                "athletes_info a \n" +
                "on \n" +
                "c.country_code = a.country_code\n" +
                "inner join \n" +
                "award_info aw\n" +
                "on\n" +
                "a.athletes_id = aw.athletes_id) tb1\n" +
                "on\n" +
                "sp.sporting_id = tb1.sporting_id\n" +
                "where sporting_name = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,sportingName);
        ResultSet rs = pstmt.executeQuery();
        List<MedalDto> list = new ArrayList<>();
        while(rs.next()){
            MedalDto m = new MedalDto();
            m.setSportingBrief(rs.getString("sporting_brief"));
            m.setAwardDate(rs.getDate("award_date"));
            m.setAwardRanking(rs.getInt("award_ranking"));
            m.setAthletesName(rs.getString("athletes_name"));
            m.setSportingName(rs.getString("sporting_name"));
            list.add(m);
        }
        return list;
    }
}
